﻿-- =============================================
-- Author:        Leon Lu
-- Create date:   2011/01/04
-- Description:   HRIS同步Employee 信息到User表
-- =============================================
Create PROCEDURE [dbo].[IMP_SYN_HRIS_Employee]
            @EmployeeNo nvarchar(20),                       -- UPI
            @NameEN nvarchar(250),                          -- CIL
            @NameCN nvarchar(250),                          -- 英文名称
            @NameChinese nvarchar(250),                     -- 中文名称
            @Title nvarchar(250),                           -- 职位
            @OfficePhone nvarchar(250),                     -- 电话
            @Email nvarchar(250),                           -- 邮箱
            @ReportTo nvarchar(20),                         -- Report To UPI
            @NTAccount nvarchar(250),                       -- NT Acount
            @BG nvarchar(20),                               -- 平台
            @BD nvarchar(20),                               -- 部门
            @Mobile nvarchar(100),                          -- 手机
            @IsActive	bit,								-- 是否有效
            @SynTime datetime                               -- 同步时间
AS 
BEGIN  
      DECLARE @errorMessage nvarchar(2000)
      set @errorMessage = '' 
      
      BEGIN TRY
            IF EXISTS (SELECT 1 FROM [User] WHERE EmployeeNo = @EmployeeNo)  
            BEGIN
                  UPDATE [User]
                     SET [EmployeeNo] = @EmployeeNo
                          --,[Department] = <Department, int,>
                          ,[NameEN] = @NameEN
                          ,[NameCN] = @NameCN
                          ,[NameChinese] = @NameChinese
                          ,[Title] = @Title
                          ,[OfficePhone] = @OfficePhone
                          ,[Email] = @Email
                          ,[ReportTo] = @ReportTo
                          ,[IsActive] = @IsActive
                          ,[Account] = @NTAccount
                          ,[BG] = @BG
                          ,[BD] = @BD
                          ,[Mobile] = @Mobile
                          ,[SyncTime] = @SynTime
                  WHERE EmployeeNo = @EmployeeNo
           END  
            ELSE
            BEGIN
            
                  INSERT INTO [User]
                                 ([EmployeeNo]
                                 ,[Account]
                                 --,[Department]
                                 ,[NameEN]
                                 ,[NameCN]
                                 ,[NameChinese]
                                 ,[Title]
                                 ,[OfficePhone]
                                 ,[Email]
                                 ,[ReportTo]
                                 ,[IsActive]
                                 ,[BG]
                                 ,[BD]
                                 ,[Mobile]
                                 ,[SyncTime])
                        VALUES
                                 (@EmployeeNo
                                 ,@NTAccount
                                 --,<Department, int,>
                                 ,@NameEN
                                 ,@NameCN
                                 ,@NameChinese
                                 ,@Title
                                 ,@OfficePhone
                                 ,@Email
                                 ,@ReportTo
                                 ,@IsActive
                                 ,@BG
                                 ,@BD
                                 ,@Mobile
                                 ,@SynTime)
                 
            END
            
      END TRY
      BEGIN CATCH
            set @errorMessage = ERROR_MESSAGE()
      END CATCH

      RETURN @errorMessage
END  
